﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DALLayer.Base;
using DataDefinition;


namespace DALLayer.Providers
{
    public class ArticleDataProvider : DataAccessBase
    {
        #region [Constructor]
        /// <summary>
        /// Initializes a new instance of the <see cref="ArticleDataProvider" /> class.
        /// </summary>
        public ArticleDataProvider() { } 

        #endregion


        #region [Public Methods]
        /// <summary>
        /// Gets articles by vehicle model data
        /// </summary>
        /// <param name="vehicleModelTypeYearId">vehicle model type year id</param>
        /// <param name="isActive">is active</param>
        /// <param name="isPublished">is published</param>
        /// <param name="vehicleModelTypeId">vehicle model type id</param>
        /// <param name="vehicleModelId">vehicle mode id</param>
        /// <param name="articleName">article name</param>
        /// <returns>data set</returns>
        public AutoFixDataSet GetSearchArticlesByVehicleModelData(int vehicleModelTypeYearId, bool isActive, bool isPublished, int vehicleModelTypeId, int vehicleModelId, string articleName)
        {
            AutoFixDataSet dataSet = null;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                dataSet = GetSearchArticlesByVehicleModelData(vehicleModelTypeYearId, isActive, isPublished, vehicleModelTypeId, vehicleModelId, articleName);
                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    //dataSet.ArticlesByVehicleModelData.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }
        /// <summary>
        /// Get article details by id or all articles
        /// </summary>
        /// <param name="articleId">article id</param>
        /// <param name="selectById">select by id</param>
        /// <returns>data set</returns>
        public AutoFixDataSet GetArticleDetails(int articleId, bool selectById)
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT Article.* FROM Article");
                //if its a selection by id
                if (selectById == true)
                {
                    //Add the sql string to retrieve data based on ArticleID
                    base.CommandTextBuilder.Append(@" WHERE (ArticleID = @ArticleID)");
                    //Add the params needed to retrieve data
                    base.DBParams.Add(@"@ArticleID", articleId);
                }

                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.Article.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }

        /// <summary>
        /// Gets the articles by user id
        /// </summary>
        /// <param name="userID">user id</param>
        /// <returns>data set</returns>
        public AutoFixDataSet GetUserArticles(int userID)
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT Article.ArticleName, Article.ArticleImageUrl, ArticleType.ArticleTypeID, Article.ArticleID, Step.StepURL, Article.IsPublished");
                base.CommandTextBuilder.Append(@" FROM (((ApplicationUser INNER JOIN
                         Article ON ApplicationUser.UserID = Article.UserID) INNER JOIN
                         ArticleType ON Article.ArticleTypeID = ArticleType.ArticleTypeID) INNER JOIN
                         Step ON Article.ArticleID = Step.ArticleID)");
                base.CommandTextBuilder.Append(@" WHERE (ApplicationUser.UserID = @UserID) AND (Article.IsActive = true) AND (ArticleType.IsActive = true) AND (ApplicationUser.IsActive = true)");

                //Add the params needed to retrieve data
                base.DBParams.Add(@"@UserID", userID);
                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.GetUserArticles.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        }
        /// <summary>
        /// Gets articles by vehicle details
        /// </summary>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <param name="vehicleModelTypeId">vehicle model type id</param>
        /// <param name="vehicleModelTypeYearId">vehicle model type year id</param>
        /// <returns>data set</returns>
        public AutoFixDataSet GetArticlesByVehicleDetails(int vehicleModelId, int vehicleModelTypeId, int vehicleModelTypeYearId, string articleName)
        {
            AutoFixDataSet dataSet = null;

            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class
                base.CommandTextBuilder.Append(@"SELECT Article.ArticleID, Article.ArticleName, Article.ArticleImageUrl");
                base.CommandTextBuilder.Append(@" FROM (((Article INNER JOIN
                         VehicleModel ON Article.VehicleModelID = VehicleModel.VehicleModelID) INNER JOIN
                         VehicleModelType ON Article.VehicleModelTypeID = VehicleModelType.VehicleModelTypeID) INNER JOIN
                         VehicleModelTypeYear ON VehicleModelType.VehicleModelTypeID = VehicleModelTypeYear.VehicleModelTypeID)");
                base.CommandTextBuilder.Append(@" WHERE (VehicleModel.VehicleModelID = @VehicleModelID) AND (VehicleModelType.VehicleModelTypeID = @VehicleModelTypeID) AND (VehicleModelTypeYear.VehicleModelTypeYearID = @VehicleModelTypeYearID) AND 
                         (Article.IsActive = True) AND (VehicleModel.IsActive = True) AND (VehicleModelType.IsActive = True) AND (VehicleModelTypeYear.IsActive = True) AND 
                         (Article.IsPublished = True) AND (Article.ArticleName LIKE '%' & @ArticleName & '%')");

                //Add the params needed to retrieve data
                base.DBParams.Add(@"@VehicleModelID", vehicleModelId);
                base.DBParams.Add(@"@VehicleModelTypeID", vehicleModelTypeId);
                base.DBParams.Add(@"@VehicleModelTypeYearID", vehicleModelTypeYearId);
                base.DBParams.Add(@"@ArticleName", articleName);
                bool success = false;
                //Check if it is success
                success = base.ExecuteReader();
                //If it is successful
                if (success == true)
                {
                    //Initial the dataset
                    dataSet = new AutoFixDataSet();
                    //Fill the table
                    dataSet.GetArticleByVehicleDetails.Load(base.DataReader);
                }
            }
            catch (Exception eX)
            {
                // log the error here
            }
            finally
            {
                //Close connection
                base.FinalizeConnection();
            }
            //Return the filled dataset
            return dataSet;
        } 

        /// <summary>
        /// The method inserts article to the table
        /// </summary>
        /// <param name="firstName">first name</param>
        /// <param name="articleImageURL">article image url</param>
        /// <param name="articleTypeId">article type id</param>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <param name="userId">user id</param>
        /// <param name="vehicleModeTypeId">vehicle model type id</param>
        /// <param name="vehicleModelTypeYearId">vehicle model type year id</param>
        /// <param name="isPublished">is published</param>
        /// <param name="lastUpdatedDate">last updated date</param>
        /// <param name="isActive">is active</param>
        /// <returns>true or false</returns>
        public bool InsertArticle(string articleName, string articleImageURL, int articleTypeId, int vehicleModelId, 
            int userId, int vehicleModeTypeId, int vehicleModelTypeYearId, bool isPublished, DateTime lastUpdatedDate, bool isActive)
        {
            
            bool success = false;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"
                    INSERT INTO Article(ArticleName, ArticleImageURL, ArticleTypeID, VehicleModelID, UserID, 
                    VehicleModelTypeID, VehicleModelTypeYearID, IsPublished, LastUpdatedDate, IsActive)
                    VALUES(@ArticleName, @ArticleImageUrl, @ArticleTypeId, @VehicleModelId, @UserId,
                         @VehicleModelTypeId, @VehicleModeTypeYearId, @IsPublished, @LastUpdatedDate, @IsActive)");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@ArticleName", articleName);
                base.DBParams.Add(@"@ArticleImageUrl", articleImageURL);
                base.DBParams.Add(@"@ArticleTypeId", articleTypeId);
                base.DBParams.Add(@"@VehicleModelId", vehicleModelId);
                base.DBParams.Add(@"@UserId", userId);
                base.DBParams.Add(@"@VehicleModelTypeId", vehicleModeTypeId);
                base.DBParams.Add(@"@VehicleModeTypeYearId", vehicleModelTypeYearId);
                base.DBParams.Add(@"@IsPublished", isPublished);
                base.DBParams.Add(@"@LastUpdatedDate", lastUpdatedDate);
                base.DBParams.Add(@"@IsActive", isActive);

                //Check if it is success
                success = base.ExecuteNonQuery();

            }
            catch (Exception)
            {
                success = false;
                //Log here
            }
            //return success
            return success;
        }

        /// <summary>
        /// The method updates article
        /// </summary>
        /// <param name="firstName">first name</param>
        /// <param name="articleImageURL">article image url</param>
        /// <param name="articleTypeId">article type id</param>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <param name="userId">user id</param>
        /// <param name="vehicleModeTypeId">vehicle model type id</param>
        /// <param name="vehicleModelTypeYearId">vehicle model type year id</param>
        /// <param name="isPublished">is published</param>
        /// <param name="lastUpdatedDate">last updated date</param>
        /// <param name="isActive">is active</param>
        /// <returns>true or false</returns>
        public bool UpdateArticle(int articleId, string firstName, string articleImageURL, int articleTypeId, int vehicleModelId,
            int userId, int vehicleModeTypeId, int vehicleModelTypeYearId, bool isPublished, DateTime lastUpdatedDate, bool isActive)
        {
            bool success = false;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"
                    UPDATE Article
                    SET ArticleName = @ArticleName, 
                        ArticleImageURL =  @ArticleImageUrl, 
                        ArticleTypeID = @ArticleTypeId, 
                        VehicleModelID =  @VehicleModelId, 
                        UserID = @UserId, 
                        VehicleModelTypeID = @VehicleModelTypeId,
                        VehicleModelTypeYearID = @VehicleModeTypeYearId, 
                        IsPublished = @IsPublished, 
                        LastUpdatedDate = @LastUpdatedDate,
                        IsActive = @IsActive
                    WHERE ArticleID = @ArticleId");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@ArticleName", firstName);
                base.DBParams.Add(@"@ArticleImageUrl", articleImageURL);
                base.DBParams.Add(@"@ArticleTypeId", articleTypeId);
                base.DBParams.Add(@"@VehicleModelId", vehicleModelId);
                base.DBParams.Add(@"@UserId", userId);
                base.DBParams.Add(@"@VehicleModelTypeId", vehicleModeTypeId);
                base.DBParams.Add(@"@VehicleModeTypeYearId", vehicleModelTypeYearId);
                base.DBParams.Add(@"@IsPublished", isPublished);
                base.DBParams.Add(@"@LastUpdatedDate", lastUpdatedDate);
                base.DBParams.Add(@"@IsActive", isActive);
                base.DBParams.Add(@"@ArticleID", articleId);

                //Check if it is success
                success = base.ExecuteNonQuery();

            }
            catch (Exception)
            {
                success = false;
                //Log here
            }
            //return success
            return success;

        }

        /// <summary>
        /// The method inserts article to the table and returns the identity
        /// </summary>
        /// <param name="firstName">first name</param>
        /// <param name="articleImageURL">article image url</param>
        /// <param name="articleTypeId">article type id</param>
        /// <param name="vehicleModelId">vehicle model id</param>
        /// <param name="userId">user id</param>
        /// <param name="vehicleModeTypeId">vehicle model type id</param>
        /// <param name="vehicleModelTypeYearId">vehicle model type year id</param>
        /// <param name="isPublished">is published</param>
        /// <param name="lastUpdatedDate">last updated date</param>
        /// <param name="isActive">is active</param>
        /// <returns>identity/articleid</returns>
        public int InsertArticleScalar(string articleName, string articleImageURL, int articleTypeId, int vehicleModelId,
            int userId, int vehicleModeTypeId, int vehicleModelTypeYearId, bool isPublished, DateTime lastUpdatedDate, bool isActive)
        {

            int result = 0;
            try
            {
                //Triggers the method in the parent class DataAccessBase
                base.InitializeDataAccess();
                //Passes the sql string that needs to be added to the property in the parent class

                base.CommandTextBuilder.Append(@"INSERT INTO Article(ArticleName, ArticleImageURL, ArticleTypeID, VehicleModelID, UserID, 
                    VehicleModelTypeID, VehicleModelTypeYearID, IsPublished, LastUpdatedDate, IsActive)
                    VALUES(@ArticleName, @ArticleImageUrl, @ArticleTypeId, @VehicleModelId, @UserId,
                         @VehicleModelTypeId, @VehicleModeTypeYearId, @IsPublished, @LastUpdatedDate, @IsActive)");
                //Add the params needed to retrieve data
                base.DBParams.Add(@"@ArticleName", articleName);
                base.DBParams.Add(@"@ArticleImageUrl", articleImageURL);
                base.DBParams.Add(@"@ArticleTypeId", articleTypeId);
                base.DBParams.Add(@"@VehicleModelId", vehicleModelId);
                base.DBParams.Add(@"@UserId", userId);
                base.DBParams.Add(@"@VehicleModelTypeId", vehicleModeTypeId);
                base.DBParams.Add(@"@VehicleModeTypeYearId", vehicleModelTypeYearId);
                base.DBParams.Add(@"@IsPublished", isPublished);
                base.DBParams.Add(@"@LastUpdatedDate", lastUpdatedDate);
                base.DBParams.Add(@"@IsActive", isActive);
                //get the result                
                result = base.ExecuteScalar();

            }
            catch (Exception)
            {
                //Log here
            }
            finally
            {
                base.FinalizeConnection();
            }
            //return success
            return result;
        }
        #endregion
       

    }
}
